<?php
/**
 * Ticket Model.
 * @package    sitrack 
 * @author     Simon Downes <simon@simondownes.co.uk>
 * @copyright  Copyright (c) 2007, Simon Downes 
 * @license    http://www.opensource.org/licenses/mit-license.php
 */

/**
 * Ticket Model.
 *
 * @package    sitrack 
 * @author     Simon Downes <simon@simondownes.co.uk>
 * @copyright  Copyright (c) 2007, Simon Downes 
 * @license    http://www.opensource.org/licenses/mit-license.php
 */
final class Ticket extends SPF_Model {

   /**
    * Constructor.
    *
    * @return  object
    */
   public function __construct() {

      /* construct the parent object */
      parent::__construct('tickets');
      
      $this->add_validation('title', true, 'custom');
      //$this->add_validation('details', false, 'alphanumeric', "\r\n". SPF::$config['allowed_chars'] );
      $this->add_validation('created_date', true, 'date');
      $this->add_validation('project_id', true, 'integer');
      $this->add_validation('component_id', false, 'integer');
      $this->add_validation('milestone_id', false, 'integer');
      $this->add_validation('ticket_type_id', true, 'integer');
      $this->add_validation('ticket_status_id', true, 'integer');
      $this->add_validation('ticket_severity_id', false, 'integer');
      $this->add_validation('ticket_priority_id', false, 'integer');
      $this->add_validation('created_user', true, 'alphanumeric');
      $this->add_validation('assigned_user', false, 'alphanumeric');
      
      // load set default values if found in registry
      $this->ticket_type_id     = (int) SPF::$registry->get('tickets.default_type');
      $this->ticket_status_id   = (int) SPF::$registry->get('tickets.default_status');
      $this->ticket_severity_id = (int) SPF::$registry->get('tickets.default_severity');
      $this->ticket_priority_id = (int) SPF::$registry->get('tickets.default_priority');
      
   } // __construct

   /**
    * Destructor.
    *
    * @return  void
    */
   public function __destruct() {
      parent::__destruct();
   }
   
   /**
    * Override the before_update() method to update the modified_date.
    *
    * @param   boolean   $new    true if the record was inserted.
    * @return  void
    */
   public function before_update( $new ) {
      
      if( $this->is_dirty() ) {
         $this->modified_date = SPF_Date::mysql_date(time(), true);
         $this->modified_user = SPF::$session->user['user_name'];
      }
      
   } // before_update
   
   /**
    * Override the after_update() method to email the assigned user if it has changed.
    *
    * @param   boolean   $new    true if the record was inserted.
    * @return  void
    */
   public function after_update( $new ) {
      
      if( $this->assigned_user && ($this->assigned_user != $this->original_data['assigned_user']) ) {
         SPF::$app->notify('assign_ticket', $this->to_array());
      }
      
      $actions = array();
      
      // if this is not a new ticket then create history records as required...
      if( !$new ) {
         
         $watch   = array('assigned_user', 'ticket_status_id', 'ticket_type_id', 'ticket_priority_id', 'ticket_severity_id', 'component_id', 'milestone_id');
         
         foreach( $watch as $field ) {
            
            if( $this->is_dirty($field) ) {
               
               switch( $field ) {
                  
                  case 'assigned_user':
                     $user  = $this->db->escape($this->assigned_user);
                     $value = SPF::$db->get_field('users', 'display_name', "user_name = '{$user}'");
                     if( !$value )
                        $value = 'no one';
                     $action = 'assigned ticket to '. $value;
                     break;
                  
                  default:
                     
                     if( substr($field, -3) == '_id' ) {
                        $table = substr($field, 0, -3);
                        $value = SPF::$db->lookup($table, $this->$field);
                        if( !$value )
                           $value = 'none';
                        $field = $table;
                     }
                     
                     if( substr($field, 0, 7) == 'ticket_' )
                        $field = substr($field, 7);
                     
                     $field = str_replace('_', ' ', $field);
                     
                     $action = 'changed '. ucwords($field). ' to '. $value;
                     
                     break;

               } // switch
               
               $actions[] = $action;
               
            } // dirty
         
         } // each $watch
         
      } // !new
      else {
         
         $actions[] = 'created ticket';
         
      }
      
      // create history records for any actions that have been performed
      if( $actions )
         self::create_history($this->id, $actions);
         
   } // after_update
   
   /**
    * Override the after_delete() method to delete the comments related to the ticket.
    *
    * @param   array     $args   primary key values of the record.
    * @return  boolean
    */
   public function after_delete( $args ) {
      
      $id = (int) array_shift($args);
      
      $sql = "DELETE
                FROM comments
               WHERE ticket_id = {$id}";
      $this->db->execute($sql);
      
   } // after_delete
   
   /**
    * Override the to_array() method to provide the display names of the created and assigned users.
    *
    * @param   boolean   $html_safe   if true then the function htmlspecialchars() will be run over each element in the returned array.
    * @return  array
    */
   public function to_array( $html_safe = false ) {
      
      $arr = parent::to_array($html_safe);
      
      $arr['formatted_created_date'] = SPF_Date::date('l, jS F Y @ g:ia', $arr['created_date']);
      $arr['formatted_modified_date'] = SPF_Date::date('l, jS F Y @ g:ia', $arr['modified_date']);
      
      $arr['created_by']  = SPF::$db->get_field('users', 'display_name', "user_name = '{$arr['created_user']}'");
      $arr['modified_by'] = SPF::$db->get_field('users', 'display_name', "user_name = '{$arr['modified_user']}'");
      $arr['assigned_to'] = SPF::$db->get_field('users', 'display_name', "user_name = '{$arr['assigned_user']}'");
      
      return $arr;
      
   } // to_array
   
   /**
    * Create ticket_history records to record any changes to a ticket.
    *
    * @param   integer   $ticket_id   the id of the ticket that was changed.
    * @param   array     $actions     an array of the actions performed.
    * @return  boolean
    */
   public static function create_history( $ticket_id, $actions ) {
      
      $ticket_id     = (int) $ticket_id;
      $now           = time();
      $modified_by   = SPF::$db->escape(SPF::$session->user['display_name']);
      
      foreach( $actions as $action ) {
         
         $action = SPF::$db->escape($action);
         
         $sql = "INSERT
                   INTO ticket_history
                    SET ticket_id     = {$ticket_id},
                        modified_date = NOW(),
                        modified_by   = '{$modified_by}',
                        action_detail = '{$action}'";
         SPF::$db->execute($sql);
      
      } // each $changes
      
   } // create_history
   
   /**
    * Returns a dataset of ticket data for the specified project and using the specified filter.
    *
    * @param   integer   $project_id   project id to return the data for.
    * @param   object    $filter       filter object used to specify which records to return.
    * @return  array
    */
   public static function browse_data( $project_id, $filter ) {
      
      $project_id = (int) $project_id;
      
      $rows_per_page = (int) SPF::$registry->get('tickets.rows_per_page', 15);
      
      if( $rows_per_page < 1 || $rows_per_page > 100 )
         $rows_per_page= 15;
      
      $where = $filter->get_sql(false);
      
      $order_by  = $filter->get_order_by();
      $order_dir = $filter->get_order_dir();
      
      switch( $order_by ) {
         
         case 'id':
            $order_by = 'tickets.id';
            break;
         
         case 'type':
            $order_by = 'ticket_type.description';
            break;
         
         case 'title':
            $order_by = 'tickets.title';
            break;
         
         case 'assigned':
            $order_by = 'assigned_user.display_name';
            break;
         
         case 'status':
            $order_by = 'ticket_status.description';
            break;
         
         case 'created':
            $order_by = 'tickets.created_date';
            break;
         
         case 'update':
            $order_by = 'tickets.modified_date';
            break;
         
         default:
            $order_by = 'tickets.id';
            $order_dir = 'DESC';
            break;
      }
      
      $sql = "SELECT tickets.id,
                     tickets.title,
                     tickets.created_date,
                     tickets.modified_date as updated_date,
                     ticket_type.description    AS type_desc,
                     ticket_status.description  AS status_desc,
                     created_user.display_name  AS created_by,
                     updated_user.display_name  AS updated_by,
                     assigned_user.display_name AS assigned_to
                FROM tickets
           LEFT JOIN ticket_type            ON ticket_type.id          = tickets.ticket_type_id
           LEFT JOIN ticket_status          ON ticket_status.id        = tickets.ticket_status_id
           LEFT JOIN users AS assigned_user ON assigned_user.user_name = tickets.assigned_user
           LEFT JOIN users AS created_user  ON created_user.user_name  = tickets.created_user
           LEFT JOIN users AS updated_user  ON updated_user.user_name  = tickets.modified_user
               WHERE tickets.project_id = {$project_id}
                     {$where}
            ORDER BY {$order_by} {$order_dir}";
      $dataset = SPF::$db->create_dataset($sql, $filter->page, $rows_per_page);
      
      foreach( $dataset['data'] as &$rec ) {
         $rec['created_date'] = SPF_Date::smart_date($rec['created_date']);
         $rec['updated_date'] = SPF_Date::smart_date($rec['updated_date']);
      }
      
      return $dataset;
      
   } // browse_data
   
   /**
    * Returns an array of data ready to be exported for the specified project and using the specified filter.
    *
    * @param   integer   $project_id   project id to return the data for.
    * @param   object    $filter       filter object used to specify which records to return.
    * @return  array
    */
   public static function export_data( $project_id, $filter ) {
      
      $project_id = (int) $project_id;
      
      $where = $filter->get_sql(false);

      $sql = "SELECT tickets.id,
                     tickets.title,
                     projects.description        AS project_desc,
                     components.description      AS component_desc,
                     milestones.description      AS milestone_desc,
                     ticket_type.description     AS type_desc,
                     ticket_status.description   AS status_desc,
                     ticket_priority.description AS priority_desc,
                     ticket_severity.description AS severity_desc,
                     assigned_user.display_name  AS assigned_to,
                     created_user.display_name   AS created_by,
                     tickets.created_date
                FROM tickets
           LEFT JOIN projects               ON projects.id             = tickets.project_id
           LEFT JOIN components             ON components.id           = tickets.component_id
           LEFT JOIN milestones             ON milestones.id           = tickets.milestone_id
           LEFT JOIN ticket_type            ON ticket_type.id          = tickets.ticket_type_id
           LEFT JOIN ticket_status          ON ticket_status.id        = tickets.ticket_status_id
           LEFT JOIN ticket_priority        ON ticket_priority.id      = tickets.ticket_priority_id
           LEFT JOIN ticket_severity        ON ticket_severity.id      = tickets.ticket_severity_id
           LEFT JOIN users AS assigned_user ON assigned_user.user_name = tickets.assigned_user
           LEFT JOIN users AS created_user  ON created_user.user_name  = tickets.created_user
               WHERE tickets.project_id = {$project_id}
                     {$where}
            ORDER BY tickets.id DESC";
      $rs = SPF::$db->get_all($sql);
      
      foreach( $rs as &$rec ) {
         $rec['created_date'] = SPF_Date::short_date($rec['created_date']);
      }
      
      return $rs;
      
   } // export_data
   
   /**
    * Returns an array of ticket data based on the specified dashboard filter.
    *
    * @param   string    $filter   filter used to specify which records to return.
    * @return  array
    */
   public static function dashboard_data( $filter ) {
      
      switch( $filter ) {
         
         case 'assigned_me':
            $where = 'tickets.assigned_user = \''. SPF::$db->escape(SPF::$session->user['user_name']). '\'';
            break;
         
         case 'created_me':
            $where = 'tickets.created_user = \''. SPF::$db->escape(SPF::$session->user['user_name']). '\'';
            break;
         
         case 'created_today':
            $where = 'tickets.created_date >= \''. SPF_Date::mysql_date(). '\'';
            break;
         
         case 'updated_today':
            $where = 'tickets.modified_Date >= \''. SPF_Date::mysql_date(). '\'';
            break;
         
      } // switch
      
      $sql = "SELECT tickets.id,
                     tickets.title,
                     tickets.created_date,
                     projects.description       AS project_desc,
                     ticket_type.description    AS type_desc,
                     ticket_status.description  AS status_desc,
                     created_user.display_name  AS created_by,
                     assigned_user.display_name AS assigned_to
                FROM tickets
           LEFT JOIN projects               ON projects.id             = tickets.project_id
           LEFT JOIN ticket_type            ON ticket_type.id          = tickets.ticket_type_id
           LEFT JOIN ticket_status          ON ticket_status.id        = tickets.ticket_status_id
           LEFT JOIN users AS assigned_user ON assigned_user.user_name = tickets.assigned_user
           LEFT JOIN users AS created_user  ON created_user.user_name  = tickets.created_user
               WHERE ticket_status.system_type = 'OPEN'
                 AND {$where}
            ORDER BY tickets.project_id ASC,
                     tickets.id DESC";
      $dataset = SPF::$db->create_dataset($sql, -1);
      
      foreach( $dataset['data'] as &$rec ) {
         $rec['created_date'] = SPF_Date::smart_date($rec['created_date']);
      }
      
      return $dataset;
      
   } // dashboard_data
   
   /**
    * Returns an array of comments for the specified ticket.
    *
    * @param   integer   $ticket_id   ticket to return the comments of.
    * @return  array
    */
   public static function get_comments( $ticket_id ) {
      
      $ticket_id = (int) $ticket_id;
      
      $sql = "SELECT comments.id,
                     comments.created_user,
                     users.display_name,
                     comments.created_date,
                     comments.details
                FROM comments
          INNER JOIN users ON users.user_name = comments.created_user
               WHERE comments.ticket_id = {$ticket_id}
            ORDER BY comments.created_date DESC";
      $rs = SPF::$db->get_all($sql);
      
      foreach( $rs as &$rec ) {
         $rec['created_time'] = SPF_Date::date('g:ia', $rec['created_date']);
         $rec['created_date'] = SPF_Date::long_date($rec['created_date']);
      }
      
      return $rs;
      
   } // get_comments
   
   /**
    * Returns an array of ticket data based on the specified dashboard filter.
    *
    * @param   integer   $ticket_id   ticket to return the history of.
    * @return  array
    */
   public static function get_history( $ticket_id ) {
      
      $ticket_id = (int) $ticket_id;
      
      $sql = "SELECT modified_date, 
                     modified_by,
                     action_detail
                FROM ticket_history
               WHERE ticket_id = {$ticket_id}
            ORDER BY modified_date DESC, id ASC";
      $rs = SPF::$db->get_all($sql);
      
      $prev_date = NULL;
      
      foreach( $rs as &$rec ) {
         $rec['modified_time'] = SPF_Date::date('g:ia', $rec['modified_date']);
         $rec['modified_date'] = SPF_Date::long_date($rec['modified_date']);
      }

      return $rs;
      
   } // get_history
   
   /**
    * Returns an array of ticket statuses with the number of tickets with each status.
    *
    * @param   integer   $ticket_id   ticket to return the attachments of.
    * @return  array
    */
   public static function get_attachments( $ticket_id ) {
      
      $ticket_id = (int) $ticket_id;
      
      $sql = "SELECT *
                FROM file_header
               WHERE file_header.ticket_id = {$ticket_id}
               ORDER BY upload_date DESC";
      $rs = SPF::$db->get_all($sql);
      
      foreach( $rs as &$attachment ) {
         
         $attachment['file_size'] = (int) $attachment['file_size'];
         
         if( $attachment['file_size'] > 1000000 )
            $attachment['file_size'] = number_format($attachment['file_size'] / 1000000, 2). ' MB';
         
         elseif( $attachment['file_size'] > 1000 )
            $attachment['file_size'] = number_format($attachment['file_size'] / 1000, 2). ' KB';
         
         else
            $attachment['file_size'] = number_format($attachment['file_size']). ' B';
         
         $attachment['uploaded'] = SPF_Date::smart_date($attachment['upload_date']);
         $attachment['uploaded']  .= ' by '. SPF::$db->get_field('users', 'display_name', "user_name = '{$attachment['upload_user']}'");
         
         switch( $attachment['system_type'] ) {
            
            case 'IMAGE':
               $attachment['document_type'] = 'Image';
               break;
            
            case 'WORD':
            case 'EXCEL':
            case 'POWERPOINT':
            case 'VISIO':
            case 'PDF':
               $attachment['document_type'] = ucfirst(strtolower($attachment['system_type'])). ' Document';
               break;

            case 'TEXT':
            case 'ZIP':
               $attachment['document_type'] = ucfirst(strtolower($attachment['system_type'])). ' File';
               break;
            
            default:
               $attachment['document_type'] = strtoupper(pathinfo($attachment['system_type'], PATHINFO_EXTENSION)). ' File';
               break;
            
         } // switch
         
      } // each attachment
      
      return $rs;
      
   } // get_attachments
   
   /**
    * Returns an array of ticket statuses with the number of tickets with each status.
    *
    * @param   integer   $ticket_id   ticket to which the attachment belongs.
    * @param   integer   $file_id     id of the attachment to return.
    * @return  array
    */
   public static function get_attachment( $ticket_id, $file_id ) {
      
      $ticket_id = (int) $ticket_id;
      $file_id   = (int) $file_id;
      
      $sql = "SELECT *
                FROM file_header
          INNER JOIN file_data ON file_data.file_header_id = file_header.id
               WHERE file_header.id = {$file_id}
                 AND file_header.ticket_id = {$ticket_id}";
      $info = SPF::$db->get_row($sql);
      
      return $info;
      
   } // get_attachment
   
   /**
    * Processes a file upload from the client.
    *
    * @param   integer   $ticket_id   ticket to which the attachment will belong.
    * @param   array     $file_info   information of file to upload.    
    * @return  array
    */
   public static function put_attachment( $ticket_id, $file_info ) {
      
      $ticket_id = (int) $ticket_id;
      
      if( !is_array($file_info) && $file_info['error'] != UPLOAD_ERR_OK )
         return false;
      
      switch( $file_info['type'] ) {
         
         case 'application/pdf':
            $system_type = 'PDF';
            break;
            
         case 'application/vnd.ms-excel':
            $system_type = 'EXCEL';
            break;
            
         case 'application/application/msword':
            $system_type = 'WORD';
            break;
            
         case 'application/vnd.visio':
            $system_type = 'VISIO';
            break;
            
         case 'application/zip':
         case 'application/x-gzip':
            $system_type = 'ZIP';
            break;
            
         default:

            if( substr($file_info['type'], 0, 5) == 'image' )  
               $system_type = 'IMAGE';
         
            elseif( substr($file_info['type'], 0, 4) == 'text' )  
               $system_type = 'TEXT';
            
            elseif( strtolower(substr($file_info['name'], -3)) == 'ppt' )  
               $system_type = 'POWERPOINT';
            
            else
               $system_type = 'OTHER';
            
            break;
         
      } // switch type
      
      $current_user      = SPF::$db->escape(SPF::$session->user['user_name']);
      $file_name         = SPF::$db->escape($file_info['name']);
      $file_info['type'] = SPF::$db->escape($file_info['type']);
      $file_info['size'] = (int) $file_info['size'];
      
      $sql = "INSERT
                INTO file_header
                 SET ticket_id   = {$ticket_id},
                     file_name   = '{$file_name}',
                     system_type = '{$system_type}',
                     mime_type   = '{$file_info['type']}',
                     upload_user = '{$current_user}',
                     upload_date = NOW(),
                     file_size   = {$file_info['size']}";
      SPF::$db->execute($sql);
      
      if( $file_header_id = SPF::$db->insert_id() ) {
         
         $file_content = SPF::$db->escape(file_get_contents($file_info['tmp_name']));
         
         $sql = "INSERT
                   INTO file_data
                    SET file_header_id = {$file_header_id},
                        file_content = '{$file_content}'";
         SPF::$db->execute($sql);
         
         self::create_history( $ticket_id, array("added attachment '{$file_info['name']}'") );
         
      }
      
      return $file_header_id;
      
   } // put_attachment
   
   /**
    * Deletes a specific attachment.
    *
    * @param   integer   $ticket_id   ticket to which the attachment belongs.
    * @param   integer   $file_id     id of the attachment to return.
    * @return  array
    */
   public static function delete_attachment( $ticket_id, $file_id ) {
      
      $ticket_id = (int) $ticket_id;
      $file_id   = (int) $file_id;
      
      $sql = "DELETE
                FROM file_header
               WHERE id        = {$file_id}
                 AND ticket_id = {$ticket_id}";
      SPF::$db->execute($sql);
      
      if( $success = SPF::$db->affected_rows() ) {
         $sql = "DELETE
                   FROM file_data
                  WHERE file_header_id = {$file_id}";
         SPF::$db->execute($sql);
      }
      
      return $success;
      
   } // delete_attachment
   
   /**
    * Returns an array of ticket statuses with the number of tickets with each status.
    *
    * @param   boolean   $simple   if true then the returned array will only include Open and Closed statuses.
    * @return  array
    */
   public static function get_count_by_status( $simple = true ) {
      
      if( $simple ) {
         $sql = "SELECT ticket_status.system_type AS 'desc',
                        COUNT(tickets.id) AS ticket_count
                   FROM ticket_status
              LEFT JOIN tickets on ticket_status.id = tickets.ticket_status_id
               GROUP BY ticket_status.system_type
               ORDER BY ticket_status.system_type";
      }
      else {
         $sql = "SELECT ticket_status.description AS 'desc',
                        COUNT(tickets.id) AS ticket_count
                   FROM ticket_status
              LEFT JOIN tickets on ticket_status.id = tickets.ticket_status_id
               GROUP BY ticket_status.id
               ORDER BY ticket_status.description";
      }
      $rs = SPF::$db->get_all($sql);
      
      $data = array();
      foreach( $rs as $rec ) {
         $data[$rec['desc']] = $rec['ticket_count'];
      }
      
      return $data;
      
   } // get_count_by_status
   
} // Ticket

?>
